package com.rent.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.fabric.xmlrpc.base.Data;
import com.rent.bean.Car;
import com.rent.bean.Rent;
import com.rent.bean.User;
import com.rent.dao.RentDao;
import com.rent.utils.BaseDao;
import com.rent.utils.DataAccessUtils;

public class RentDaoImpl extends BaseDao implements RentDao{
	
	DataAccessUtils d = new DataAccessUtils();
	
	@Override
	public List<Rent> sele(User u) {
		List<Rent> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from rent_car where uname = ?");
		ResultSet rs = executeQuery(sql.toString(), u.getUname());
		try {
			list = d.parseRent(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}

	@Override
	public int add(Rent r) {
		String sql = "insert into rent values(null,?,?,?,?,?,?,?,?,0,0,null,null);";
		return executeUpdate(sql, r.getUname(),r.getRtime(),r.getBtime(),r.getMoney(),r.getStatus(),r.getCid(),r.getCar_deposit(),r.getViolation_deposit());
	}

	@Override
	public List<Rent> seleAll() {
		List<Rent> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from rent_car;");
		ResultSet rs = executeQuery(sql.toString());
		try {
			list = list = d.parseRent(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}
	
	

	@Override
	public List<Rent> seleByUname(String uname) {
		List<Rent> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from rent_car where uname = ?;");
		ResultSet rs = executeQuery(sql.toString(),uname);
		try {
			list = list = d.parseRent(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}

	@Override
	public List<Rent> seleByUname(String uname,int status) {
		List<Rent> list = new ArrayList<>();
		String sql = "select * from rent_car where 1=1";
		if(status!=0) {
			sql+=" and status = "+status;
		}
		if(uname!=null) {
			sql+=" and uname = '"+uname+"'";
		}
		ResultSet rs = executeQuery(sql);
		try {
			list = list = d.parseRent(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}
	@Override
	public List<Rent> seleStatusByUname(String uname) {
		List<Rent> list = new ArrayList<>();
		String sql = "select * from rent_car where status in (1,2,3) and uname =?;";
		ResultSet rs = executeQuery(sql,uname);
		try {
			list = list = d.parseRent(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}

	@Override
	public int updateStatusByUname(Rent r) {
		String sql = "update rent set status = ? where rid = ?;";
		return executeUpdate(sql,r.getStatus(),r.getRid());
	}

	@Override
	public int cancel(Rent r) {
		//取消订单应使用事务一起执行
		Connection conn = getConnection();
		String sql = "update rent set status = 5 where rid = ?;";
		String sql3 = "update car_new set cstatus=1 where carid=?;";
		try {
			conn.setAutoCommit(false);
			PreparedStatement ps1 = conn.prepareStatement(sql);
			PreparedStatement ps3 = conn.prepareStatement(sql3);
			ps1.setInt(1, r.getRid());
			ps3.setInt(1, r.getCid());
			ps1.executeUpdate();
			ps3.executeUpdate();
			//未支付情况不用退钱
			if(r.getStatus()!=1){
				String sql2 = "update user set balance=balance+? where uname=?;";
				PreparedStatement ps2 = conn.prepareStatement(sql2);
				ps2.setDouble(1,(r.getMoney()+r.getCar_deposit()+r.getViolation_deposit()));
				ps2.setString(2, r.getUname());
				ps2.executeUpdate();
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
				closeAll(conn, null, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 1;
	}

	@Override
	public int rentCar(Rent r) {
		//修改订单的状态并且根据状态退还用户的押金
		Connection conn = getConnection();
		String sql = "update rent set status=?,car_deposit=?,car_deposit_status=?,btime=?,money=? where rid=?;";
		try {
			conn.setAutoCommit(false);
			PreparedStatement p1 = conn.prepareStatement(sql);
			p1.setInt(1, r.getStatus());
			p1.setDouble(2, r.getCar_deposit());
			p1.setInt(3, r.getCar_deposit_status());
			p1.setString(4, r.getBtime());
			p1.setDouble(5, r.getMoney());
			p1.setInt(6, r.getRid());
			p1.executeUpdate();
			if(r.getCar_deposit_status()==1) {
				//退还汽车押金
				String sqlUser = "update user set balance=balance+? where uname=?;";
				PreparedStatement p2 = conn.prepareStatement(sqlUser);
				p2.setDouble(1, r.getCar_deposit());
				p2.setString(2, r.getUname());
				p2.executeUpdate();
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
				closeAll(conn, null, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 1;
	}

	@Override
	public int rentUpdateRtime(Rent r) {
		Connection conn = getConnection();
		String sql_rent = "update rent set rtime=?,money=money+? where rid=?;";
		String sql_user = "update user set balance=balance-? where uname=?;";
		PreparedStatement psr = null;
		PreparedStatement psu  = null;
		try {
			conn.setAutoCommit(false);
			psr = conn.prepareStatement(sql_rent);
			psu = conn.prepareStatement(sql_user);
			psr.setString(1,r.getRtime());
			psr.setDouble(2, r.getMoney());
			psr.setInt(3, r.getRid());
			psu.setDouble(1, r.getMoney());
			psu.setString(2, r.getUname());
			int r1 = psr.executeUpdate();
			int r2 = psu.executeUpdate();
			if(r1==1&&r2==1) {
				conn.commit();
			}else {
				throw new SQLException();
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
				closeAll(null,psu,null);
				closeAll(conn,psr,null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 1;
	}

	@Override
	public List<Rent> seleDeposit(String uname) {
		List<Rent> list = new ArrayList<>();
		String sql = "select * from rent_car where (car_deposit_status=0 || violation_deposit_status=0)";
		if(uname!=null) {
			sql+=" and uname ='"+uname+"'";
		}
		ResultSet rs = executeQuery(sql);
		try {
			list = list = d.parseRent(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}

	@Override
	public Rent seleById(int id) {
		Rent r = null;
		String sql = "select * from rent_car where rid=?";
		ResultSet rs = executeQuery(sql,id);
		try {
			List<Rent> parseRent = d.parseRent(rs);
			if(!parseRent.isEmpty()) {
				r = parseRent.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return r;
	}

	@Override
	public int updateCarDeposit(Rent r) {
		Connection conn = getConnection();
		String sqlr = "update rent set car_deposit_status=1 where rid = ?";
		String sqlu = "update user set balance=balance+? where uname = ?";
		PreparedStatement psu = null;
		PreparedStatement psr = null;
		try {
			conn.setAutoCommit(false);
			psr = conn.prepareStatement(sqlr);
			psr.setInt(1, r.getRid());
			psu = conn.prepareStatement(sqlu);
			psu.setDouble(1, r.getCar_deposit());
			psu.setString(2, r.getUname());
			int r1 = psr.executeUpdate();
			int r2 = psu.executeUpdate();
			if(r1==1&&r2==1) {
				conn.commit();
			}else {
				throw new SQLException();
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
				closeAll(null,psu,null);
				closeAll(conn,psr,null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 1;
	}

	@Override
	public int updateViolationDeposit(Rent r) {
		Connection conn = getConnection();
		String sqlr = "update rent set violation_deposit_status=1 where rid = ?";
		String sqlu = "update user set balance=balance+? where uname = ?";
		PreparedStatement psu = null;
		PreparedStatement psr = null;
		try {
			conn.setAutoCommit(false);
			psr = conn.prepareStatement(sqlr);
			psr.setInt(1, r.getRid());
			psu = conn.prepareStatement(sqlu);
			psu.setDouble(1, r.getViolation_deposit());
			psu.setString(2, r.getUname());
			int r1 = psr.executeUpdate();
			int r2 = psu.executeUpdate();
			if(r1==1&&r2==1) {
				conn.commit();
			}else {
				throw new SQLException();
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
				closeAll(null,psu,null);
				closeAll(conn,psr,null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 1;
	}

	@Override
	public int updateCarDepositDescribe(Rent r) {
		String sql = "update rent set car_deposit_describe=? where rid=?;";
		return executeUpdate(sql,r.getCar_deposit_describe(),r.getRid());
	}

	@Override
	public int updateViolationDepositDescribe(Rent r) {
		String sql = "update rent set violation_deposit_describe=? where rid=?;";
		return executeUpdate(sql,r.getViolation_deposit_describe(),r.getRid());
	}

	@Override
	public int seleToDay() {
		String sql = "select count(1) `day` from rent where to_days(rtime)=to_days(now());";
		ResultSet rs = executeQuery(sql);
		int count = 0;
		try {
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return count;
	}

	@Override
	public int seleToMonth() {
		String sql = "select count(1) from rent WHERE DATE_FORMAT( rtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );";
		ResultSet rs = executeQuery(sql);
		int count = 0;
		try {
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return count;
	}

	@Override
	public int seleSumMoney() {
		String sql = "select sum(money) from rent where status=4";
		ResultSet rs = executeQuery(sql);
		int sum = 0;
		try {
			if(rs.next()) {
				sum = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return sum;
	}

	@Override
	public List<Rent> seleByTime(String startTime, String endTime) {
		String sql = "select * from rent_car where rtime between ? and ? ";
		ResultSet rs = executeQuery(sql, startTime,endTime);
		List<Rent> parseRent = d.parseRent(rs);
		closeAll(rs);
		return parseRent;
	}

	@Override
	public List<Rent> seleByStatus(int status) {
		String sql = "select * from rent_car where status=? ";
		ResultSet rs = executeQuery(sql,status);
		List<Rent> parseRent = d.parseRent(rs);
		closeAll(rs);
		return parseRent;
	}

	@Override
	public double seleToMonthMoney() {
		String sql = "select sum(money) from rent where status=4 and DATE_FORMAT( rtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
		ResultSet rs = executeQuery(sql);
		double sum = 0;
		try {
			if(rs.next()) {
				sum = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return sum;
	}

	@Override
	public double seleToDayMoney() {
		String sql = "select sum(money) from rent where status=4 and to_days(rtime)=to_days(now())";
		ResultSet rs = executeQuery(sql);
		double sum = 0;
		try {
			if(rs.next()) {
				sum = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return sum;
	}
	
	
	
	
	
}
